﻿//======================================================================
//
//        Copyright (C) 苏州逸仙软件    
//        All rights reserved
//
//        filename :D_T_Experience_Exp.cs
//        description :经验分享功能页面
//
//        created by 邹兴武 at  2010-12-30
//
//======================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace huaguwang
{
    public class D_T_Experience_Exp : D_T_Experience
    {

        /// <summary>
        /// 查找分页数据
        /// </summary>
        /// <param name="begin">起始记录数</param>
        /// <param name="pagesize">页大小</param>
        /// <param name="title">搜索关键字:标题</param>
        /// <param name="catetitle">搜索关键字:类别名称</param>
        /// <param name="count">总记录数</param>
        /// <returns>查询结果数据</returns>
        public List<huaguwang.M_T_Experience_Exp.clsFind> GetList(int begin, int pagesize, string title, string catetitle, out int count)
        {
            var tab = " dbo.T_Experience t1,dbo.T_Category t2,dbo.T_Member t3,dbo.T_AdminUser t4 ";
            var con = "  t1.Flag = 1 and t2.Flag = 1 and t3.Flag = 1 and t4.Flag = 1 and t1.CateID = t2.id and t1.memberid = t3.id and t4.id = t1.UserID";
            if (!string.IsNullOrEmpty(title))
            {
                con += " and t1.Title like '%'+@title+'%' ";
            }
            if (!string.IsNullOrEmpty(catetitle))
            {
                con += " and t2.CateName like '%'+@catetitle+'%' ";
            }

            SqlParameter[] parms = { 
                                   new SqlParameter("@title",SqlDbType.NVarChar,50),
                                   new SqlParameter("@catetitle",SqlDbType.NVarChar,50)
                                   };
            parms[0].Value = title.Exp_Trim();
            parms[1].Value = catetitle.Exp_Trim();

            var countSql = ExpandMSSQL.GetPageSQL(tab, con);
            count = MSSQLHelper.SQLConType.C1.Exp_GetSingle(
                countSql,
                parms
                ).Exp_IntTryParse();

            string strSQL = ExpandMSSQL.GetPageSQL(
                   begin,
                   pagesize,
                   "t1.id,t1.Title,t2.CateName,t1.State,t3.Name as memname,t1.RemSign,t1.Topis,t1.UploadDate,t4.LoginName",
                   tab,
                   "t1.id",
                  con,
                   "t1.id desc"
                   );

            var List = MSSQLHelper.SQLConType.C1.Exp_GetList(
                strSQL,
                DR => new huaguwang.M_T_Experience_Exp.clsFind()
                {
                    id = DR["id"].Exp_IntTryParse(),
                    Title = DR["Title"].Exp_Trim(),
                    CateTitle = DR["CateName"].Exp_Trim(),
                    MemName = DR["memname"].Exp_Trim(),
                    State = DR["State"].Exp_BoolTryParse()?"正常":"封冻",
                    Topis = DR["Topis"].Exp_IntTryParse()>0?"置顶":"未置顶",
                    RemSign = DR["RemSign"].Exp_BoolTryParse()?"已推荐":"未推荐",
                    dtime = DR["UploadDate"].Exp_DateTimeTryParse().ToString("yyyy-MM-dd"),
                    UserName = DR["LoginName"].Exp_Trim()
                },
                parms
                );
            return List;
        }

        /// <summary>
        /// 对选定内容的状态进行处理
        /// </summary>
        /// <param name="list">要处理的对象List对象</param>
        /// <param name="flag">操作的值:true/false</param>
        /// <returns>成功的行数</returns>
        public int UpdateState(List<int> list, bool flag)
        {
            var Refitem = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                var intID = 0;
                var Gid = 0;
                string strSql = " update T_Experience set state=@state where id=@id ";
                SqlParameter[] parameters = {
                    new SqlParameter("@state", SqlDbType.Bit),
                    new SqlParameter("@id", SqlDbType.Int,4)};

                foreach (var id in list)
                {
                    parameters[0].Value = flag;
                    parameters[1].Value = id; 
                    com.Parameters.Add(parameters[0]);
                    com.Parameters.Add(parameters[1]);
                    com.CommandText = strSql.ToString();
                    intID = com.ExecuteNonQuery();
                    com.Parameters.Clear();
                    if (0 >= intID)
                    {
                        tran.Rollback();
                        return 0;
                    }
                    Gid++;
                }
                tran.Commit();
                return Gid;
            }
          );
            return Refitem;
        }

        /// <summary>
        /// 对选定内容的推荐进行处理
        /// </summary>
        /// <param name="list">要处理的对象List对象</param>
        /// <param name="flag">操作的值:true/false</param>
        /// <returns>成功的行数</returns>
        public int UpdateRem(List<int> list, bool flag)
        {
            var Refitem = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                var intID = 0;
                var Gid = 0;
                string strSql = " update T_Experience set RemSign=@remsign where id=@id ";
                SqlParameter[] parameters = {
                    new SqlParameter("@remsign", SqlDbType.Bit),
                    new SqlParameter("@id", SqlDbType.Int,4)};

                foreach (var id in list)
                {
                    parameters[0].Value = flag;
                    parameters[1].Value = id;
                    com.Parameters.Add(parameters[0]);
                    com.Parameters.Add(parameters[1]);
                    com.CommandText = strSql.ToString();
                    intID = com.ExecuteNonQuery();
                    com.Parameters.Clear();
                    if (0 >= intID)
                    {
                        tran.Rollback();
                        return 0;
                    }
                    Gid++;
                }
                tran.Commit();
                return Gid;
            }
          );
            return Refitem;
        }

        /// <summary>
        /// 对选定内容的置顶进行处理
        /// </summary>
        /// <param name="list">要处理的对象List对象</param>
        /// <param name="flag">操作的值:true/false</param>
        /// <returns>成功的行数</returns>
        public int UpdateTopis(List<int> list, int flag)
        { 
            var Refitem = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                var intID = 0;
                var Gid = 0;
                string strSql = " update T_Experience set Topis=@topis where id=@id ";
                SqlParameter[] parameters = {
                    new SqlParameter("@topis", SqlDbType.Int,4),
                    new SqlParameter("@id", SqlDbType.Int,4)};

                foreach (var id in list)
                {
                    parameters[0].Value = flag;
                    parameters[1].Value = id;
                    com.Parameters.Add(parameters[0]);
                    com.Parameters.Add(parameters[1]);
                    com.CommandText = strSql.ToString();
                    intID = com.ExecuteNonQuery();
                    com.Parameters.Clear();
                    if (0 >= intID)
                    {
                        tran.Rollback();
                        return 0;
                    }
                    Gid++;
                }
                tran.Commit();
                return Gid;
            }
          );
            return Refitem;
        }

    }
}
